CREATE OR REPLACE FUNCTION "public"."get_user_questions"("username_param" "text") RETURNS TABLE("id" bigint, "title" "text", "slug" "text", "total_useful" bigint)
    LANGUAGE "plpgsql"
    SET search_path = public, pg_temp
    AS $$
BEGIN
  RETURN QUERY
  SELECT 
    q.id,
    q.title,
    q.slug,
    COALESCE(COUNT(uv.id), 0)::BIGINT AS total_useful
  FROM questions q
  INNER JOIN profiles p ON p.id = q.created_by
  LEFT JOIN useful_votes uv ON uv.content_id = q.id AND uv.content_type = 'questions'
  WHERE p.username = username_param
  AND (q.deleted IS NULL OR q.deleted = FALSE)
  GROUP BY q.id, q.title, q.slug;
END;
$$;

CREATE TABLE IF NOT EXISTS "public"."questions" (
    "id" bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "created_at" timestamp with time zone DEFAULT ("now"() AT TIME ZONE 'utc'::"text") NOT NULL,
    "created_by" bigint,
    "deleted" boolean,
    "modified_at" timestamp with time zone DEFAULT ("now"() AT TIME ZONE 'utc'::"text") NOT NULL,
    "comment_count" bigint DEFAULT '0'::bigint,
    "description" "text" NOT NULL,
    "moderation" "text",
    "slug" "text" NOT NULL,
    "previous_slugs" "text"[],
    "category" bigint,
    "tags" bigint[],
    "title" "text" NOT NULL,
    "total_views" bigint,
    "tenant_id" "text" NOT NULL,
    "fts" "tsvector" GENERATED ALWAYS AS ("to_tsvector"('"english"'::"regconfig", (("title" || ' '::"text") || "description"))) STORED,
    "images" "json"[],
    "legacy_id" "text",
    "is_draft" boolean DEFAULT false NOT NULL
);

CREATE OR REPLACE FUNCTION "public"."questions_search_fields"("public"."questions") RETURNS "text"
    LANGUAGE "sql"
    SET search_path = public, pg_temp
    AS $_$
  SELECT $1.title || ' ' || $1.description;
$_$;

ALTER TABLE ONLY "public"."questions"
    ADD CONSTRAINT "unique_tenant_slug" UNIQUE ("tenant_id", "slug");

CREATE INDEX "questions_category_idx" ON "public"."questions" USING "btree" ("category");
CREATE INDEX "questions_created_by_idx" ON "public"."questions" USING "btree" ("created_by");
CREATE INDEX "questions_deleted_moderation_category_total_views_tags_crea_idx" ON "public"."questions" USING "btree" ("deleted", "moderation", "category", "total_views", "tags", "created_at", "comment_count", "created_by");
CREATE INDEX "questions_tags_idx" ON "public"."questions" USING "gin" ("tags");

ALTER TABLE ONLY "public"."questions"
    ADD CONSTRAINT "question_created_by_fkey" FOREIGN KEY ("created_by") REFERENCES "public"."profiles"("id") ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE ONLY "public"."questions"
    ADD CONSTRAINT "questions_category_fkey" FOREIGN KEY ("category") REFERENCES "public"."categories"("id") ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE "public"."questions" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "tenant_isolation" ON "public"."questions" USING (("tenant_id" = ((SELECT current_setting('request.headers'::"text", true))::"json" ->> 'x-tenant-id'::"text")));
